#include"db.h"

// 清空输入缓冲区
void clear_input_buffer() {
    int c;
    while ((c = getchar()) != '\n' && c != EOF);
}


// 保存用户（完全重写，避免清空表操作）
void save_users(UserDatabase *db) {
    sqlite3_exec(db->db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
    
    char *err_msg = NULL;
    int rc;
    
    // 1. 更新或插入每个用户
    const char *upsert_sql = "INSERT OR REPLACE INTO users (id, username, password, is_admin, class_name, phone, email) "
                            "VALUES (?, ?, ?, ?, ?, ?, ?);";
    sqlite3_stmt *stmt;
    
    rc = sqlite3_prepare_v2(db->db, upsert_sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备Upsert语句失败: %s\n", sqlite3_errmsg(db->db));
        sqlite3_exec(db->db, "ROLLBACK;", NULL, NULL, NULL);
        return;
    }
    
    for (int i = 0; i < db->count; i++) {
        User *u = &db->users[i];
        
        // 绑定参数
        sqlite3_bind_text(stmt, 1, u->id, -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 2, u->username, -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 3, u->password, -1, SQLITE_STATIC);
        sqlite3_bind_int(stmt, 4, u->is_admin);
        
        // 处理可能为空的字段
        if (u->class_name[0]) {
            sqlite3_bind_text(stmt, 5, u->class_name, -1, SQLITE_STATIC);
        } else {
            sqlite3_bind_null(stmt, 5);
        }
        
        if (u->phone[0]) {
            sqlite3_bind_text(stmt, 6, u->phone, -1, SQLITE_STATIC);
        } else {
            sqlite3_bind_null(stmt, 6);
        }
        
        if (u->email[0]) {
            sqlite3_bind_text(stmt, 7, u->email, -1, SQLITE_STATIC);
        } else {
            sqlite3_bind_null(stmt, 7);
        }
        
        // 执行插入或更新
        rc = sqlite3_step(stmt);
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "保存用户 %s 失败: %s\n", u->username, sqlite3_errmsg(db->db));
            sqlite3_finalize(stmt);
            sqlite3_exec(db->db, "ROLLBACK;", NULL, NULL, NULL);
            return;
        }
        
        sqlite3_reset(stmt);
    }
    
    sqlite3_finalize(stmt);
    
    // 2. 删除不再存在的用户
    // 构建现有用户ID列表
    char id_list[20000] = "(";  // 假设最多1000个用户，每个ID最长20字符
    int first = 1;
    for (int i = 0; i < db->count; i++) {
        if (!first) {
            strcat(id_list, ",");
        }
        strcat(id_list, "'");
        strcat(id_list, db->users[i].id);
        strcat(id_list, "'");
        first = 0;
    }
    strcat(id_list, ")");
    
    char delete_sql[25000];
    snprintf(delete_sql, sizeof(delete_sql), 
             "DELETE FROM users WHERE id NOT IN %s;", id_list);
    
    rc = sqlite3_exec(db->db, delete_sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "清理旧用户失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_exec(db->db, "ROLLBACK;", NULL, NULL, NULL);
        return;
    }
    
    // 提交事务
    sqlite3_exec(db->db, "COMMIT;", NULL, NULL, NULL);
    printf("已保存 %d 个用户到数据库\n", db->count);
}


// 添加学生（使用统一初始密码）
void add_student(UserDatabase *db) {
    if (db->count >= MAX_USERS) {
        printf("错误：用户数量已达上限 %d\n", MAX_USERS);
        return;
    }

    User new_student = {0};
    printf("\n=== 添加学生 ===\n");
    
    // 输入学号
    while (1) {
        printf("学号: ");
        scanf("%19s", new_student.id);
        clear_input_buffer();
        
        int exists = 0;
        for (int i = 0; i < db->count; i++) {
            if (strcmp(db->users[i].id, new_student.id) == 0)
            {
                exists = 1;
                break;
            }
        }
        if (!exists) break;
        printf("错误：学号已存在，请重新输入\n");
    }
    
    // 输入用户名
    while (1) {
        printf("用户名: ");
        scanf("%49s", new_student.username);
        clear_input_buffer();
        
        int exists = 0;
        for (int i = 0; i < db->count; i++) {
            if (strcmp(db->users[i].username, new_student.username) == 0) {
                exists = 1;
                break;
            }
        }
        if (!exists) break;
        printf("错误：用户名已存在，请重新输入\n");
    }
    
    // 使用统一初始密码
    strcpy(new_student.password, DEFAULT_STUDENT_PASSWORD);
    printf("初始密码: %s（请告知学生登录后修改）\n", DEFAULT_STUDENT_PASSWORD);
    
    new_student.is_admin = 0;
    
    printf("班级: ");
    scanf("%29s", new_student.class_name);
    clear_input_buffer();
    
    printf("电话: ");
    scanf("%19s", new_student.phone);
    clear_input_buffer();
    
    printf("邮箱: ");
    scanf("%49s", new_student.email);
    clear_input_buffer();
    
    //new_student.math = new_student.english = new_student.science = -1;
    
    db->users[db->count++] = new_student;
    save_users(db);
    
    printf("\n学生添加成功！\n学号: %s\n用户名: %s\n", 
           new_student.id, new_student.username);
}

// 查看学生列表
void view_students(UserDatabase *db) {
    printf("\n=== 学生列表 ===\n");
    printf("%-15s   %-15s    %-15s    %-20s    %-15s\n", 
           "学号", "用户名", "班级", "邮箱", "电话");
    
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin) {
            //float avg = (db->users[i].math + db->users[i].english + db->users[i].science) / 3;
            printf("%-15s %-15s %-15s %-20s %-15s ", 
                   db->users[i].id,
                   db->users[i].username,
                   db->users[i].class_name,
                   db->users[i].email,
                   db->users[i].phone);

            printf("\n");
            
            /*if (db->users[i].math >= 0 && db->users[i].english >= 0 && db->users[i].science >= 0) {
                printf("%-10.1f\n", avg);
            } else {
                printf("%-10s\n", "未录入");
            }*/
        }
    }
}

// 修改学生信息
void update_student(UserDatabase *db)
{
    char id[20];
    printf("\n请输入要更新的学生学号: ");
    scanf("%19s", id);
    clear_input_buffer();
    
    int index = -1;
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin && strcmp(db->users[i].id, id) == 0) 
        {
            index = i;
            break;
        }
    }
    
    if (index == -1) {
        printf("错误：未找到该学生\n");
        return;
    }

    User *student = &db->users[index];
    
    printf("\n=== 修改学生信息 ===\n");
    printf("学号: %s\n", student->id);
    
    printf("当前用户名: %s\n新用户名: ", student->username);
    scanf("%49s", student->username);
    clear_input_buffer();
    
    printf("当前班级: %s\n新班级: ", student->class_name);
    scanf("%29s", student->class_name);
    clear_input_buffer();
    
    printf("当前电话: %s\n新电话: ", student->phone);
    scanf("%19s", student->phone);
    clear_input_buffer();
    
    printf("当前邮箱: %s\n新邮箱: ", student->email);
    scanf("%49s", student->email);
    clear_input_buffer();
    
    save_users(db);
    printf("\n学生信息已更新！\n");
}

// 删除学生（修复外键约束问题）
void delete_student(UserDatabase *db) {
    char id[20];
    printf("\n请输入要删除的学生学号: ");
    scanf("%19s", id);
    clear_input_buffer();
    
    int index = -1;
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin && strcmp(db->users[i].id, id) == 0)
        {
            index = i;
            break;
        }
    }
    
    if (index == -1) {
        printf("错误：未找到该学生\n");
        return;
    }

    char confirm;
    printf("\n确定要删除学生 %s (学号: %s) 吗？(y/n): ", 
           db->users[index].username, db->users[index].id);
    scanf(" %c", &confirm);
    clear_input_buffer();
    
    if (confirm == 'y' || confirm == 'Y') {
        // 开始事务
        sqlite3_exec(db->db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
        
        // 先删除成绩记录
        char *err_msg = NULL;
        char sql[100];
        snprintf(sql, sizeof(sql), "DELETE FROM grades WHERE id = '%s';", db->users[index].id);
        int rc = sqlite3_exec(db->db, sql, NULL, NULL, &err_msg);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "删除成绩记录失败: %s\n", err_msg);
            sqlite3_free(err_msg);
            sqlite3_exec(db->db, "ROLLBACK;", NULL, NULL, NULL);
            return;
        }
        
        // 再删除用户记录
        snprintf(sql, sizeof(sql), "DELETE FROM users WHERE id = '%s';", db->users[index].id);
        rc = sqlite3_exec(db->db, sql, NULL, NULL, &err_msg);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "删除用户记录失败: %s\n", err_msg);
            sqlite3_free(err_msg);
            sqlite3_exec(db->db, "ROLLBACK;", NULL, NULL, NULL);
            return;
        }
        
        // 提交事务
        sqlite3_exec(db->db, "COMMIT;", NULL, NULL, NULL);
        
        // 从内存数组中移除
        for (int i = index; i < db->count - 1; i++) {
            db->users[i] = db->users[i + 1];
        }
        db->count--;
        
        printf("学生已删除\n");
    } else {
        printf("删除操作已取消\n");
    }
}
